﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;

namespace DataAccess
{
    public class UserDAO
    {
        public const int DUTY_WAITER = 1;
        public const int DUTY_CHEF = 2;
        public const int DUTY_MANAGER = 3;
        public const int DUTY_OTHER = 4;

        /**
         * Judge the user is exist in db.
         * @param {string} username The login id
         * @param {strng} password The password of this uername with un-encrypt by MD5.
         */
        public bool isUserExist(string username, string password)
        {
            SqlConnection con = ConnectionManager.getInstance().getConnection();
            
            // Encrypt password text
            MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
            byte[] b = new byte[128];
            b = md5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(password));
            StringBuilder strB = new StringBuilder();
            for (int m = 0; m < b.Length; m++)
                strB.Append(b[m].ToString("X2"));
            password = strB.ToString();

            // Create a sql command object.
            string strSql = string.Format("select staffID from tb_staff where staffID='{0}' and pwd='{1}';",
                        username, password);
            SqlCommand command = new SqlCommand(strSql, con);
            SqlDataReader sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
            if (sdr.Read())
            {
                return true;
            }

            return false;
        }

        public bool isChefExist(string username, string password)
        {
            SqlConnection con = ConnectionManager.getInstance().getConnection();

            // Encrypt password text
            MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
            byte[] b = new byte[128];
            b = md5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(password));
            StringBuilder strB = new StringBuilder();
            for (int m = 0; m < b.Length; m++)
                strB.Append(b[m].ToString("X2"));
            password = strB.ToString();

            // Create a sql command object.
            string strSql = string.Format("select staffID from tb_staff where staffID='{0}' and pwd='{1}' and duty={2};",
                        username, password, DUTY_CHEF);
            SqlCommand command = new SqlCommand(strSql, con);
            SqlDataReader sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
            if (sdr.Read())
            {
                return true;
            }

            return false;
        }

        /**
         * Get User Permission string.
         */
        public String getPermission(string staffID)
        {
            // Create a sql command object.
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                string strSql = string.Format("select permission from tb_staff where staffID='{0}'", staffID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (sdr.Read())
                {
                    return sdr["permission"].ToString().Trim();
                }
                return null;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        /**
         * Modify password of staffID.
         */
        public void updatePwd(string staffID, string newPwd)
        {
            // Create a sql command object.
            SqlConnection con = null;
            try
            {
                // Encrypt password text
                MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
                byte[] b = new byte[128];
                b = md5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(newPwd));
                StringBuilder strB = new StringBuilder();
                for (int m = 0; m < b.Length; m++)
                    strB.Append(b[m].ToString("X2"));
                newPwd = strB.ToString();

                con = ConnectionManager.getInstance().getConnection();
                string strSql = string.Format("UPDATE tb_staff SET pwd='{0}' WHERE staffID='{1}';",
                                                            newPwd,
                                                            staffID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        // todo other operation...
        public DataSet getAllUser()
        {
            DataSet ds = new DataSet();
            SqlConnection con = null;
            try
            {
                con = DataAccess.ConnectionManager.getInstance().getConnection();
                SqlDataAdapter objDA = new SqlDataAdapter("pr_GetAllUser", con);
                objDA.SelectCommand.CommandType = CommandType.StoredProcedure;
                objDA.Fill(ds, "tb_staff");
                return ds;
            }
            catch (Exception e1)
            {
                throw e1;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        public Object getUser(string strStaffID)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();

                // create a sql command object
                string strSql = String.Format(
                                                "SELECT staffID,staffName, duty" +
                                                " FROM tb_staff WHERE staffID='{0}'", strStaffID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                Hashtable ht = new Hashtable();
                if (sdr.Read())
                {
                    ht.Add("staffID", sdr["staffID"]);
                    ht.Add("staffName", sdr["staffName"]);
                    ht.Add("duty", sdr["duty"]);
                }
                return ht;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        public bool isLoginIDExist(string strLoginID)
        {
            SqlConnection con = null;
            SqlDataReader sdr = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();
                string strSql = String.Format("select COUNT(staffID) from tb_staff where staffID='{0}';", strLoginID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                sdr = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    if (Int32.Parse(sdr[0].ToString()) > 0)
                    {
                        return true;
                    }
                }
                return false;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (sdr != null) { sdr.Close(); sdr.Dispose(); sdr = null; };
                if (con != null) { con.Close(); con.Dispose(); con = null; };
            }
        }

        public void addNewUser(string strOperName, string strStaffID, string strName, string pwd, string permission, int position)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();

                // Encrypt password text
                MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
                byte[] b = new byte[128];
                b = md5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(pwd));
                StringBuilder strB = new StringBuilder();
                for (int m = 0; m < b.Length; m++)
                    strB.Append(b[m].ToString("X2"));
                pwd = strB.ToString();

                string strSql = string.Format("insert into tb_staff(staffID, staffName, pwd, permission, duty) values(" +
                                                            "'{0}','{1}', '{2}', '{3}', {4});",
                                                            strStaffID, strName, pwd, permission, position);
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();

                string strDetails = "添加 用户: " + strStaffID +" 成功";
                RMS.Logger.Add(strOperName, strStaffID, RMS.Logger.INFO);
            }
            catch (Exception e)
            {
                string strDetails = "添加 用户: " + strStaffID + "失败，详细日志：" + e.Message;
                RMS.Logger.Add(strOperName, strStaffID, RMS.Logger.ERROR);
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        public void updatePermission(string strOperName, string strStaffID, string permission)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();

                string strSql = string.Format("update tb_staff set permission='{0}'where staffID='{1}';",
                                                           permission, strStaffID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();

                string strDetails = "修改 用户: " + strStaffID + " 权限成功";
                RMS.Logger.Add(strOperName, strStaffID, RMS.Logger.INFO);
            }
            catch (Exception e)
            {
                string strDetails = "修改 用户: " + strStaffID + "权限失败，详细日志：" + e.Message;
                RMS.Logger.Add(strOperName, strStaffID, RMS.Logger.ERROR);
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        public void removeUser(string strOperName, string strStaffID)
        {
            SqlConnection con = null;
            try
            {
                con = ConnectionManager.getInstance().getConnection();

                string strSql = string.Format("delete from tb_staff where staffID='{0}';", strStaffID);
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();

                string strDetails = "删除 用户: " + strStaffID + " 成功";
                RMS.Logger.Add(strOperName, strStaffID, RMS.Logger.INFO);
            }
            catch (Exception e)
            {
                string strDetails = "删除 用户: " + strStaffID + "失败，详细日志：" + e.Message;
                RMS.Logger.Add(strOperName, strStaffID, RMS.Logger.ERROR);
                throw e;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                    con = null;
                }
            }
        }

        public static string convertToStrDuty(int dutytype)
        {
            switch (dutytype)
            {
                case DUTY_WAITER :
                    return "服务员";
                case DUTY_CHEF :
                    return "厨师";
                case DUTY_MANAGER :
                    return "管理人员";
                case DUTY_OTHER :
                    return "其它人员";
                default :
                    throw new Exception("该类型有误，无法读取");
            }
        }
    }
}
